package net.sppan.blog.controller.admin;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import net.sppan.blog.annotation.RequiresPermissions;
import net.sppan.blog.controller.BaseController;
import net.sppan.blog.model.ClickStats;
import net.sppan.blog.model.Res;
import net.sppan.blog.model.Role;
import net.sppan.blog.model.User;
import net.sppan.blog.utils.Const;
import net.sppan.blog.utils.ObjectUtils;
import net.sppan.blog.utils.SimpleDateFormatUtil;
import net.sppan.blog.utils.WebUtils;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.jfinal.aop.Clear;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;

@RequiresPermissions
public class AdminController extends BaseController {

	@RequiresPermissions(key = "admin:index")
	public void index() {
		int pageNumber = getParaToInt("pageNumber", 1);
		int pageSize = getParaToInt("pageSize", 100);
		User user = WebUtils.currentUser(this);
		Role loginUserRole = (Role) getSession().getAttribute("loginUserRole");
		String searchAgent = getPara("search_agentname");
		String searchCustomer = getPara("search_username");
		String searchOfferId = getPara("search_offerid");
		String searchDate = getPara("search_date");
		String sql = "";
		if (loginUserRole.getId().equals(Const.AdministratorId)) {
			setAttr("admin", "admin");
			sql = " from sys_click_stats where 1=1";
			List<User> customers = User.dao.findUserByRoleId(Const.CustomerId);
			setAttr("customers", customers);
			if (StrKit.notBlank(searchAgent) && !"0".equals(searchAgent)) {
				setAttr("search_agentname", searchAgent);
				sql += " and agentId=" + searchAgent;
				customers = User.dao.find("select * from sys_user where parent=" + searchAgent);
				setAttr("customers", customers);
			}
		} else {
			setAttr("currentAgent", user);
			List<User> customers = User.dao.find("select * from sys_user where parent=" + user.getId());
			setAttr("customers", customers);
			sql = " from sys_click_stats where agentId=" + user.getId();
		}
		if (StrKit.notBlank(searchCustomer) && !"-1".equals(searchCustomer)) {
			setAttr("search_username", searchCustomer);
			sql += " and userId=" + searchCustomer;
		}
		if (StrKit.notBlank(searchOfferId)) {
			setAttr("search_offerid", searchOfferId);
			sql += " and offerId=" + searchOfferId;
		}
		if (StrKit.notBlank(searchDate)) {
			setAttr("search_date", searchDate);
			String[] ds = searchDate.split(" - ");
			String beginDate = ds[0].replace("-", "");
			String endDate = ds[1].replace("-", "");
			sql += " and statsDay between " + beginDate + " and " + endDate;
		} else {
			//把时间固定到昨天
			Calendar cal = Calendar.getInstance();
			cal.add(Calendar.DATE, -1);
			String yesterday = new SimpleDateFormat("yyyy-MM-dd ").format(cal.getTime()).replace("-", "");
			setAttr("search_date", yesterday + " - " + yesterday);
			sql += " and statsDay >= " + yesterday + " and statsDay<=" + yesterday;
		}
		String countSql = sql;
		sql += " group by statsDay,userId,userName,agentId,agentName,offerId,price";
		sql += " order by statsDay desc";
		List<User> agents = User.dao.findUserByRoleId(Const.AgentId);
		setAttr("agents", agents);
		Page<ClickStats> page = null;
		Record record = Db.findFirst("select sum(successClick) as click,sum(successClick*price) as revenue  " + countSql + " and buckle=0");
		BigDecimal nonDeClick = record.getBigDecimal("click") == null ? new BigDecimal(0D) : record.getBigDecimal("click");
		Double nonDeRevenue = record.getDouble("revenue") == null ? 0D : record.getDouble("revenue");
		if (loginUserRole.getId().equals(Const.AdministratorId)) {
			page = ClickStats.dao.paginate(pageNumber, pageSize, "SELECT statsDay,userId,userName,agentId,agentName,offerId,price,SUM( CASE WHEN buckle = '1' THEN  successClick ELSE 0 END) as buckle,  SUM( CASE WHEN buckle = '0' THEN successClick ELSE 0 END)  as successClick ",
					sql);
			Record deductRecord = Db.findFirst("select sum(successClick) as click,sum(successClick*price) as revenue  " + countSql + " and buckle=1");
			BigDecimal deductClick = deductRecord.getBigDecimal("click") == null ? new BigDecimal(0D) : deductRecord.getBigDecimal("click");
			Double deductRevenue = deductRecord.getDouble("revenue") == null ? 0D : deductRecord.getDouble("revenue");
			setAttr("totalClick", nonDeClick.add(deductClick));
			setAttr("totalRevenue", ObjectUtils.DoubleAdd(nonDeRevenue, deductRevenue));
			setAttr("deductClick", deductClick);
			setAttr("deductRevenue", deductRevenue);
		} else {
			page = ClickStats.dao.customPaginate(sql, pageNumber, pageSize);
			setAttr("totalClick", nonDeClick);
			setAttr("totalRevenue", nonDeRevenue);
		}
		setAttr("pageList", page);
		render("welcome.html");
	}

	@RequiresPermissions(key = "admin:index")
	public void export() {
		User user = WebUtils.currentUser(this);
		Role loginUserRole = (Role) getSession().getAttribute("loginUserRole");
		String searchAgent = getPara("search_agentname");
		String searchCustomer = getPara("search_username");
		String searchOfferId = getPara("search_offerid");
		String searchDate = getPara("search_date");
		String sql = "";
		if (loginUserRole.getId().equals(Const.AdministratorId)) {
			setAttr("admin", "admin");
			sql = " from sys_click_stats where 1=1";
			List<User> customers = User.dao.findUserByRoleId(Const.CustomerId);
			setAttr("customers", customers);
			if (StrKit.notBlank(searchAgent) && !"0".equals(searchAgent)) {
				setAttr("search_agentname", searchAgent);
				sql += " and agentId=" + searchAgent;
			}
		} else {
			setAttr("currentAgent", user);
			List<User> customers = User.dao.find("select * from sys_user where parent=" + user.getId());
			setAttr("customers", customers);
			sql = " from sys_click_stats where agentId=" + user.getId();
		}
		if (StrKit.notBlank(searchCustomer) && !"-1".equals(searchCustomer)) {
			setAttr("search_username", searchCustomer);
			sql += " and userId=" + searchCustomer;
		}
		if (StrKit.notBlank(searchOfferId)) {
			setAttr("search_offerid", searchOfferId);
			sql += " and offerId=" + searchOfferId;
		}
		if (StrKit.notBlank(searchDate)) {
			setAttr("search_date", searchDate);
			String[] ds = searchDate.split(" - ");
			String beginDate = ds[0].replace("-", "");
			String endDate = ds[1].replace("-", "");
			sql += " and statsDay between " + beginDate + " and " + endDate;
		} else {
			//把时间固定到昨天
			Calendar cal = Calendar.getInstance();
			cal.add(Calendar.DATE, -1);
			String yesterday = new SimpleDateFormat("yyyy-MM-dd ").format(cal.getTime()).replace("-", "");
			setAttr("search_date", yesterday + " - " + yesterday);
			sql += " and statsDay between " + yesterday + " and " + yesterday;
		}
		sql += " group by statsDay,userId,userName,agentId,agentName,offerId,price";
		sql += " order by statsDay desc";
		List<ClickStats> stats = new ArrayList<>();
		if (loginUserRole.getId() == Const.AdministratorId) {
			stats = ClickStats.dao.find("SELECT statsDay,userId,userName,agentId,agentName,offerId,price,SUM( CASE WHEN buckle = '1' THEN  successClick ELSE 0 END) as buckle,  SUM( CASE WHEN buckle = '0' THEN successClick ELSE 0 END)  as successClick " + sql);
		} else {
			String sbsql = "select * from(SELECT statsDay,userId,userName,agentId,agentName,offerId,price,SUM( CASE WHEN buckle = '1' THEN  successClick ELSE 0 END) as buckle,  SUM( CASE WHEN buckle = '0' THEN successClick ELSE 0 END)  as successClick "
					+ sql
					+ ") as t where successClick>0 ";
			stats = ClickStats.dao.find(sbsql);
		}

		if (ObjectUtils.isNotEmpty(stats)) {
			//生成Excel
			String excelName = "stats-" + SimpleDateFormatUtil.format(new Date(), SimpleDateFormatUtil.ymd) + ".xls";
			HSSFWorkbook wb = new HSSFWorkbook();
			HSSFSheet sheet = wb.createSheet();
			HSSFCellStyle cellStyleCenter = ExportFileNameUtils.initColumnHeadStyle(wb);//表头样式
			HSSFCellStyle cellStyleRight = ExportFileNameUtils.initColumnCenterstyle(wb);//单元格样式
			HSSFCellStyle cellStyleLeft = ExportFileNameUtils.initColumnCenterstyle(wb);
			cellStyleRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT);//右对齐
			cellStyleLeft.setAlignment(HSSFCellStyle.ALIGN_LEFT);//左对齐

			if (loginUserRole.getId() == Const.AdministratorId) {
				// 设置列宽
				sheet.setColumnWidth(0, 5000);
				sheet.setColumnWidth(1, 5000);
				sheet.setColumnWidth(2, 5000);
				sheet.setColumnWidth(3, 5000);
				sheet.setColumnWidth(4, 5000);
				sheet.setColumnWidth(5, 5000);
				sheet.setColumnWidth(6, 5000);
				sheet.setColumnWidth(7, 5000);
				sheet.setColumnWidth(8, 5000);
				HSSFRow row = sheet.createRow(0);
				HSSFCell cell = null;
				cell = row.createCell(0);
				cell.setCellValue("statsDay");
				cell.setCellStyle(cellStyleCenter);
				cell = row.createCell(1);
				cell.setCellValue("agentId");
				cell.setCellStyle(cellStyleCenter);
				cell = row.createCell(2);
				cell.setCellValue("agentName");
				cell.setCellStyle(cellStyleCenter);
				cell = row.createCell(3);
				cell.setCellValue("userId");
				cell.setCellStyle(cellStyleCenter);
				cell = row.createCell(4);
				cell.setCellValue("userName");
				cell.setCellStyle(cellStyleCenter);
				cell = row.createCell(5);
				cell.setCellValue("deviceId");
				cell.setCellStyle(cellStyleCenter);
				cell = row.createCell(6);
				cell.setCellValue("offerId");
				cell.setCellStyle(cellStyleCenter);
				cell = row.createCell(7);
				cell.setCellValue("price");
				cell.setCellStyle(cellStyleCenter);
				sheet.setColumnWidth(9, 5000);
				cell = row.createCell(8);
				cell.setCellValue("deduct_click");
				cell.setCellStyle(cellStyleCenter);
				cell = row.createCell(9);
				cell.setCellValue("total_click");
				cell.setCellStyle(cellStyleCenter);

				int rowNum = 1;
				for (ClickStats click : stats) {
					row = sheet.createRow(rowNum);
					cell = row.createCell(0);
					cell.setCellValue(click.getStatsDay());
					cell.setCellStyle(cellStyleCenter);
					cell = row.createCell(1);
					cell.setCellValue(click.getAgentId());
					cell.setCellStyle(cellStyleCenter);
					cell = row.createCell(2);
					cell.setCellValue(click.getAgentName());
					cell.setCellStyle(cellStyleCenter);
					cell = row.createCell(3);
					cell.setCellValue(click.getUserId());
					cell.setCellStyle(cellStyleCenter);
					cell = row.createCell(4);
					cell.setCellValue(click.getUserName());
					cell.setCellStyle(cellStyleCenter);
					cell = row.createCell(5);
					cell.setCellValue(click.getDeviceId());
					cell.setCellStyle(cellStyleCenter);
					cell = row.createCell(6);
					cell.setCellValue(click.getOfferId());
					cell.setCellStyle(cellStyleCenter);
					cell = row.createCell(7);
					cell.setCellValue(click.getPrice());
					cell.setCellStyle(cellStyleCenter);
					cell = row.createCell(8);
					cell.setCellValue(click.getBuckle().doubleValue());
					cell.setCellStyle(cellStyleCenter);
					cell = row.createCell(9);
					cell.setCellValue(click.getSuccessClick().intValue() + click.getBuckle().intValue());
					cell.setCellStyle(cellStyleCenter);
					rowNum++;
				}
			} else {
				// 设置列宽
				sheet.setColumnWidth(0, 5000);
				sheet.setColumnWidth(1, 5000);
				sheet.setColumnWidth(2, 5000);
				sheet.setColumnWidth(3, 5000);
				HSSFRow row = sheet.createRow(0);
				HSSFCell cell = null;
				cell = row.createCell(0);
				cell.setCellValue("statsDay");
				cell.setCellStyle(cellStyleCenter);
				cell = row.createCell(1);
				cell.setCellValue("userName");
				cell.setCellStyle(cellStyleCenter);
				cell = row.createCell(2);
				cell.setCellValue("offerId");
				cell.setCellStyle(cellStyleCenter);
				cell = row.createCell(3);
				cell.setCellValue("total_click");
				cell.setCellStyle(cellStyleCenter);

				int rowNum = 1;
				for (ClickStats click : stats) {
					row = sheet.createRow(rowNum);
					cell = row.createCell(0);
					cell.setCellValue(click.getStatsDay());
					cell.setCellStyle(cellStyleCenter);
					cell = row.createCell(1);
					cell.setCellValue(click.getUserName());
					cell.setCellStyle(cellStyleCenter);
					cell = row.createCell(2);
					cell.setCellValue(click.getOfferId());
					cell.setCellStyle(cellStyleCenter);
					cell = row.createCell(3);
					cell.setCellValue(click.getSuccessClick().intValue());
					cell.setCellStyle(cellStyleCenter);
					rowNum++;
				}
			}

			try {
				ByteArrayOutputStream os = new ByteArrayOutputStream();
				wb.write(os);
				byte[] fileContent = os.toByteArray();
				ByteArrayInputStream is = new ByteArrayInputStream(fileContent);
				getResponse().setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(excelName, "UTF-8"));
				OutputStream out = getResponse().getOutputStream();
				//创建缓冲区
				byte buffer[] = new byte[1024];
				int len = 0;
				//循环将输入流中的内容读取到缓冲区当中
				while ((len = is.read(buffer)) > 0) {
					//输出缓冲区的内容到浏览器，实现文件下载
					out.write(buffer, 0, len);
				}
				//关闭文件输入流
				is.close();
				//关闭输出流
				out.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		} else {
			renderJson(false, "导出的数据为空!");
			return;
		}
		renderNull();
		return;
	}

	@Clear
	public void login_page() {
		render("login.html");
	}

	/**
	 * 执行登录
	 */
	@Clear
	public void login() {
		String username = getPara("username");
		String password = getPara("password");
		String remember = getPara("remember", "0");
		Role role = null;
		System.out.println(WebUtils.pwdEncode(password));
		User user = User.dao.login(username, WebUtils.pwdEncode(password));
		if (StrKit.notNull(user)) {
			try {
				role = Role.dao.findRoleByUserId(user.getId());
				int roleId = role.getId();
				if (roleId == Const.CustomerId) {
					renderJson(false, "no permission");
					return;
				}
				//FreeMarkerRender.getConfiguration().setSharedVariable("menu", Res.dao.getResourceList(user.getId(), null));
				getRequest().getSession().setAttribute("menu", Res.dao.getResourceList(user.getId(), null));
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			user.update();
			try {
				setSessionAttr("loginUserRole", role);
				WebUtils.loginUser(this, user, remember.equals("1"));
				setSessionAttr("loginUser", user);
			} catch (UnsupportedEncodingException e) {
				e.printStackTrace();
				renderJson(false, e.getMessage());
				return;
			}
			renderJson(true, "Login successful");
		} else {
			renderJson(false, "Login failed, username or password error");
		}
	}

	/**
	 * 执行登出
	 * @return void	返回类型
	 * @throws
	 */
	@Clear
	public void logout() {
		WebUtils.logoutUser(this);
		redirect("/admin/index");
	}

	@RequiresPermissions(key = "admin:index")
	public void onChange() {
		String agentId = getPara("id");
		List<User> users = new ArrayList<>();
		if ("0".equals(agentId)) {
			users = User.dao.findUserByRoleId(Const.CustomerId);
			renderJson(users);
			return;
		} else {
			users = User.dao.find("select * from sys_user where parent=" + agentId);
			renderJson(users);
			return;
		}
	}
}
